﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using System.Data.SqlClient;


namespace YNCNumberSys.db
{
    public class CommandManager
    {
        /// <summary>
        /// 更新数据，内部增加事务的概念。XSL ADD 2022 11 17 ,查了一遍，不合适的地方改了。
        /// </summary>
        /// <param name="conn">数据库连接。</param>
        /// <param name="strSql">SQL语句。</param>
        /// <param name="strTableName">数据库的表名。</param>
        /// <returns></returns>
        public DataTable ExecuteNonQuery(SqlConnection conn, string strSql, string strTableName)
        {
            SqlTransaction trans = null;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 300;
                SqlDataAdapter oda = new SqlDataAdapter();
                DataTable dt = new DataTable();
                cmd.CommandText = strSql;
                cmd.CommandType = CommandType.Text;
                trans = conn.BeginTransaction();
                cmd.Transaction = trans;
                oda.SelectCommand = cmd;
                oda.Fill(dt);
                dt.TableName = strTableName;
                trans.Commit();
                return dt;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return null;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) 
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 更新数据,有事务的概念，事务在外部统一提价。内部不关闭conn。 XSL Mod 2022 11 核查了一遍，确认内部无提示。
        ///  cnn=connectionManager.getConnection();
        ///  trans=  cnn.BeginTransaction();
        ///  ExecuteNonQuery...
        ///  ExecuteNonQuery...
        ///  ...
        ///  trans.Commit();
        ///  finally  {cnn.Close();}
        /// </summary>
        /// <param name="conn">连接，连接外部的事务。</param>
        /// <param name="tran">事务，通常是链接的事务。</param>
        /// <param name="strSql">SQL语句。</param>
        /// <param name="strTableName">表名。</param>
        /// <returns></returns>
        public DataTable ExecuteNonQuery(SqlConnection conn, SqlTransaction tran, string strSql, string strTableName)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 300;
                cmd.CommandText = strSql;
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = tran;
                SqlDataAdapter oda = new SqlDataAdapter();
                DataTable dt = new DataTable();
                oda.SelectCommand = cmd;
                oda.Fill(dt);
                dt.TableName = strTableName;
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) //conn!=null
                {
                 //   conn.Close();
                }
            }
        }
        /// <summary>
        /// 执行单条语句，这个在程序中没调用。参数含义见 public DataTable ExecuteNonQuery(SqlConnection conn, SqlTransaction tran, string strSql, string strTableName)
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int ExecuteScalar(SqlConnection conn, SqlTransaction tran, string strSql)
        {

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 300;
                cmd.CommandText = strSql;
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = tran;
                return cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) //conn!=null
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 程序只用了一处，类内部调用，外部未调用，好像是遍历表。SQL 还是select，改的意义不大，不在内部加事务了。
        /// conn在函数里面关闭。
        /// </summary>
        /// <param name="conn">数据库连接。</param>
        /// <param name="strSql">SQL语句。</param>
        /// <returns></returns>
        public int ExecuteScalar(SqlConnection conn, string strSql)
        {
            int returnValue;
            SqlTransaction trans = null;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 300;
                cmd.CommandText = strSql;
                cmd.CommandType = CommandType.Text;
                trans = conn.BeginTransaction();
                cmd.Transaction = trans;
                returnValue = cmd.ExecuteNonQuery();
                trans.Commit();
                return returnValue;
            }
            catch (Exception ex)
            {
                if(trans!=null)
                  trans.Rollback();
                return 0;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) 
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 许树亮内部加事务。XSL 核查一遍。2022 11
        /// </summary>
        /// <param name="conn">数据库连接。</param>
        /// <param name="strSql">SQL语句。</param>
        /// <returns></returns>
        public int update(SqlConnection conn, string strSql)
        {
            int updateCount = 0;
            SqlTransaction trans = null;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandTimeout = 300;
                trans = conn.BeginTransaction();//XSL ADD 2022 2 20
                cmd.Transaction = trans;//XSL ADD 2022 2 20
                cmd.CommandText = strSql;
                cmd.CommandType = CommandType.Text;
                updateCount = cmd.ExecuteNonQuery();
                trans.Commit();//XSL ADD 2022 11 17
                return updateCount;
            }
            catch (Exception)
            {
                if (trans != null) trans.Rollback();
                return 0;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) //conn!=null
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// XSL 核查一遍。2022 11
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tran"></param>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int update(SqlConnection conn, SqlTransaction tran, string strSql)
        {
            int updateCount = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = strSql;
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = tran;
                updateCount = cmd.ExecuteNonQuery();
                return updateCount;
            }
            catch (Exception)
            {
                return 0;// throw;
            }
            finally
            {
            }
        }
        public int update(SqlConnection conn, List<String> strSql)
        {
            SqlTransaction tran = null;
            int updateCount = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                SqlCommand cmd = conn.CreateCommand();
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                for (int i = 0; i <= strSql.Count - 1; i++)
                {
                    cmd.CommandText = strSql[i];
                    cmd.CommandType = CommandType.Text;
                    updateCount += cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return updateCount;
            }
            catch (Exception)
            {
                try
                {
                    if (tran != null)
                    {
                        tran.Rollback();
                    }
                }
                catch (Exception)
                {

                    throw;
                }
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) //conn!=null
                {
                    conn.Close();
                }
            }
        }
        public int update(SqlConnection conn, SqlTransaction tran, List<String> strSql)
        {
            int updateCount = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = conn.CreateCommand();

                cmd.Transaction = tran;

                for (int i = 0; i <= strSql.Count - 1; i++)
                {
                    cmd.CommandText = strSql[i];

                    cmd.CommandType = CommandType.Text;
                    updateCount += cmd.ExecuteNonQuery();
                }

                return updateCount;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) //conn!=null
                {
                    conn.Close();
                }
            }
        }
        public int update(SqlConnection conn, DataTable dt, string[] updateColumnsName)
        {
            SqlTransaction tran = null;
            int updateCount = 0;
            StringBuilder sbSql = new StringBuilder("");
            try
            {
                sbSql.Append(" SELECT [");

                if (updateColumnsName == null || updateColumnsName.Length <= 0)
                {
                    for (int i = 0; i <= dt.Columns.Count - 1; i++)
                    {
                        sbSql.Append(dt.Columns[i].ColumnName + "],[");
                    }
                }
                else
                {
                    for (int i = 0; i <= updateColumnsName.Length - 1; i++)
                    {
                        sbSql.Append(updateColumnsName[i] + "],[");
                    }
                }

                sbSql.Remove(sbSql.Length - 2, 2);
                sbSql.Append(" FROM ");
                sbSql.Append(" " + dt.TableName + " ");
                sbSql.Append(" WHERE 1= 2 ");

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter oda = new SqlDataAdapter();
                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(oda);
                tran = conn.BeginTransaction();
                cmd.CommandText = sbSql.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = tran;
                oda.SelectCommand = cmd;
                oda.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
                oda.InsertCommand = sqlCommandBuilder.GetInsertCommand();
                oda.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();
                oda.UpdateCommand.Transaction = tran;
                oda.InsertCommand.Transaction = tran;
                oda.DeleteCommand.Transaction = tran;
                updateCount = oda.Update(dt);
                tran.Commit();
            }
            catch (Exception ex)
            {
                updateCount = -1;
                try
                {
                    if (tran != null)
                    {
                        tran.Rollback();
                    }
                }
                catch (Exception ex2)
                {

                    throw ex2;
                }
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) //conn!=null
                {
                    conn.Close();
                }
            }
            return updateCount;
        }
        public int update(SqlConnection conn, SqlTransaction tran, DataTable dt, string[] updateColumnsName)
        {
            int updateCount = 0;
            StringBuilder sbSql = new StringBuilder("");
            try
            {
                sbSql.Append(" SELECT ");

                if (updateColumnsName == null || updateColumnsName.Length <= 0)
                {
                    for (int i = 0; i <= dt.Columns.Count - 1; i++)
                    {
                        sbSql.Append(dt.Columns[i].ColumnName + ",");
                    }
                }
                else
                {
                    for (int i = 0; i <= updateColumnsName.Length - 1; i++)
                    {
                        sbSql.Append(updateColumnsName[i] + ",");
                    }
                }

                sbSql.Remove(sbSql.Length - 1, 1);
                sbSql.Append(" FROM ");
                sbSql.Append(" " + dt.TableName + " ");
                sbSql.Append(" WHERE 1= 2 ");

                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter oda = new SqlDataAdapter();
                SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(oda);
                cmd.CommandText = sbSql.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = tran;

                oda.SelectCommand = cmd;

                oda.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
                oda.InsertCommand = sqlCommandBuilder.GetInsertCommand();
                oda.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();

                oda.UpdateCommand.Transaction = tran;
                oda.InsertCommand.Transaction = tran;
                oda.DeleteCommand.Transaction = tran;

                updateCount = oda.Update(dt);

                return updateCount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open) //conn!=null
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 内部调用，闭环。不用查。
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <param name="projectID"></param>
        /// <returns></returns>
        public DataTable GetTable(SqlConnection conn,string tableName,string projectID)
        {
            StringBuilder strSql = new StringBuilder("");
            strSql.AppendLine(" SELECT *");
            strSql.AppendLine(" FROM " + tableName);
            strSql.AppendLine(" WHERE 1=1");
            if(!string.IsNullOrEmpty(projectID))
                strSql.AppendLine("AND 项目ID = '" + projectID + "'");
            return ExecuteNonQuery(conn, strSql.ToString(), tableName);
        }
        public DataTable GetTable2(SqlConnection conn, string tableName, int id)
        {
            
                StringBuilder strSql = new StringBuilder("");
                strSql.AppendLine(" SELECT *");
                strSql.AppendLine(" FROM " + tableName);
                strSql.AppendLine(" WHERE 1=1");
                strSql.AppendLine(" AND id = " + id);
                return ExecuteNonQuery(conn, strSql.ToString(), tableName);

        }
    }
}
